<?php

// No direct access
defined ('_JEXEC') or die();

jimport ('joomla.application.component.modellist');

class ShipnhanhModelOrders extends JModelList
{
	private $selectQuery = 'o.id, u.id AS userid, u.username, o.link, o.usorderid, o.usprice, o.tax, o.shipus, o.fee,
				w.name AS webname, ((usprice + usprice * tax / 100 + shipus) * quantity) as webcharge,
				o.vnprice, o.surcharge, os.description as status, os.color as status_color, o.note, o.quantity, c.name as customer,
				 c.email,
				o.photo, o.productname, o.orderdate, c.phone, c.address, o.size, o.color';
	
	public function __construct($config = array())
	{
		if (empty($config['filter_fields'])) {
			$config['filter_fields'] = array('id', 'username', 'status', 'webname');
		}
		parent::__construct($config);
	}
	
	function getListQuery()
	{
		$db = JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->select($this->selectQuery);
		$query->from('#__sn_orders AS o');
		$query->join('INNER', '#__users AS u ON (u.id = o.userid)');
		$query->join('INNER', '#__sn_webs AS w on (w.id = o.webid)');
		$query->join('INNER', '#__sn_order_status AS os ON (os.id = o.status)');
		$query->join('LEFT', '#__sn_contact_info AS c ON (o.contact_info_id = c.id)');
		
		// Filter search
		$search = $this->getState('filter.search');
		if (!empty($search))
		{
			$where = "";
			
			if (is_numeric($search))
				$where .= " o.id=" . $db->getEscaped($search) . " OR ";
			
			$where .= " c.name LIKE " . $db->quote('%' . $search . '%') . " OR ";
			$where .= " o.usorderid LIKE " . $db->quote('%' . $search . '%') . " OR ";
			$where .= " o.productname LIKE" . $db->quote('%' . $search . '%') . " OR ";
			$where .= " c.email LIKE" . $db->quote('%' . $search . '%');
			$query->where($where);
		}
		
		// Filter status
		$status = $this->getState('filter.status');
		if (!empty($status))
		{
			$query->where('o.status='.$status);
		}
		
		// Filter web
		$web = $this->getState('filter.web');
		if (!empty($web))
		{
			$query->where('o.webid='.$web);
		}
		
		$query->order($this->getState('list.ordering', 'o.id') .
				' ' . $this->getState('list.direction', 'ASC'));
		
		return $query;
	}
	
	protected function populateState($ordering = null, $direction = null)
	{
		// Load the filter state
		$search = $this->getUserStateFromRequest($this->context.'.filter.search', 'filter_search');
		$this->setState('filter.search', $search);
		
		//Filter (dropdown) order status
		$state = $this->getUserStateFromRequest($this->context.'.filter.status', 'filter_status', '', 'string');
		$this->setState('filter.status', $state);
		
		//Filter (dropdown) web
		$web = $this->getUserStateFromRequest($this->context.'.filter.web', 'filter_web', '', 'string');
		$this->setState('filter.web', $web);
		
		parent::populateState($ordering, $direction);
	}
	
	public function getData($ids = array(), $getListQuery = false)
	{
		$db = JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->select($this->selectQuery);
		$query->from('#__sn_orders AS o');
		$query->join('INNER', '#__users AS u ON (u.id = o.userid)');
		$query->join('INNER', '#__sn_webs AS w on (w.id = o.webid)');
		$query->join('INNER', '#__sn_order_status AS os ON (os.id = o.status)');
		$query->join('LEFT', '#__sn_contact_info AS c ON (o.contact_info_id = c.id)');
		
		// Filter search
		$search = $this->getState('filter.search');
		if (!empty($search))
		{
			$search = $db->quote('%'. $db->escape($search, true) . '%');
			$query->where('o.productname LIKE ' . $search);
		}
		
		// Filter status
		$status = $this->getState('filter.status');
		if (!empty($status))
		{
			$query->where('o.status='.$status);
		}
		
		// Filter web
		$web = $this->getState('filter.web');
		if (!empty($web))
		{
			$query->where('o.webid='.$web);
		}
		
		$query->order($this->getState('list.ordering', 'o.id') .
				' ' . $this->getState('list.direction', 'ASC'));		
		
		$size = count($ids);
		
		if ($size > 0)
		{
			$idInSet = "(";
			
			for ($i = 0; $i < $size - 1; $i++)
			{
				$idInSet .= $ids[$i].", ";
			}
			
			$idInSet .= $ids[$size - 1];
			
			$idInSet .= ")";
			
			$query->where('o.id IN ' . $idInSet);
		}
		
		$db->setQuery($query);
			
		return $db->loadObjectList();
		
	}
	
	public function countCustomer($ids)
	{
		$db = JFactory::getDBO();
		$query = $db->getQuery(true);
		$size = count($ids);
		
		if ($size > 0)
		{
			$query->select('COUNT(DISTINCT contact_info_id)');
			$query->from('#__sn_orders');
			$idInSet = "(";
				
			for ($i = 0; $i < $size - 1; $i++)
			{
				$idInSet .= $ids[$i].", ";
			}
				
			$idInSet .= $ids[$size - 1];
				
			$idInSet .= ")";
				
			$query->where('id IN ' . $idInSet);
			
			$db->setQuery($query);
			
			return $db->loadResult();
		}
		
		return 0;
	}
	
	public function getSumOf($ids = array(), $fieldname = "")
	{
		$db = JFactory::getDBO();
		$query = $db->getQuery(true);
		$query->select('SUM('. $fieldname . ')');
		$query->from('#__sn_orders');
		
		$size = count($ids);
		
		if ($size > 0)
		{
			$idInSet = "(";
				
			for ($i = 0; $i < $size - 1; $i++)
			{
			$idInSet .= $ids[$i].", ";
			}
				
			$idInSet .= $ids[$size - 1];
				
			$idInSet .= ")";
				
			$query->where('id IN ' . $idInSet);
		}
		
		$db->setQuery($query);
				
		return $db->loadResult();
	}
	
	public function updateDataField($ids = array(), $field, $value)
	{
		$db = JFactory::getDBO();
		$query = $db->getQuery(true);
		
		if (!isset($field) || count($ids) == 0)
			return;
		
		$query->update('#__sn_orders');
		$query->set($field. '=' . $value);
		
		$size = count($ids);
		$idInSet = "(";
		
		for ($i = 0; $i < $size - 1; $i++)
		{
		$idInSet .= $ids[$i].", ";
		}
	
		$idInSet .= $ids[$size - 1];
	
		$idInSet .= ")";
	
		$query->where('id IN ' . $idInSet);
		
		$db->setQuery($query);
		$db->query();
	}
}